iT邦幫忙

2021 iThome 鐵人賽

DAY 9
0

前言

今天要來練習SQL的查詢語法

使用的軟體是SQLite Studio

我上的課程是郭耀仁老師在台大資訊系統訓練班所開設的『SQL資料分析』

這篇文章會使用老師上課教到的觀念

利用kaggle上的資料實際操作

會分成三個部分,分別是:

  • 函數的介紹
  • 通用函數
  • 聚合函數

使用的資料是kaggle上一筆五百人的身高體重紀錄

500 Person Gender-Height-Weight-Body Mass Index
先來看看前十筆資料長怎樣

SELECT *
 FROM data
 LIMIT 10;


另外一筆資料是netflix上的紀錄

https://www.kaggle.com/sarahjeeeze/imdbfile

也來看看資料長怎樣

SELECT *
 FROM record
 LIMIT 10;

函數類型

函數可依照功能分為兩類

  • 通用函數
    • 轉換資料類型。
    • 計算數值。
    • 操作文字。
    • 操作日期時間。
  • 聚合函數

通用函數與聚合函數的不同在於其所作用的維度

  • 通用函數作用會顯示在水平方向。

    像是每個觀測值會對應到一個輸出

    假如現在想讓身高變成公尺的形式

SELECT *,
       Height*0.01 AS height_in_meters
 FROM data
 LIMIT 10;

可以看到最右邊那欄的將左邊的身高一一輸出成公尺的形式

  • 聚合函數整合在垂直方向

    就是整欄變數最後會對應到一個輸出

    SELECT AVG(Height) AS avg_height
     FROM data;
    

可以通過聚合函數計算整體的平均身高

通用函數

舉例來說,要計算每個人的BMI值

BMI = 體重(公斤) / 身高^2(公尺)^2

SELECT *,
      Weight/((Height*0.01)*(Height*0.01))AS BMI
 FROM data
 LIMIT 10;

可以看到出來的BMI小數點位數很多

若想要限制輸出的小數點位數

ROUND 函數: 調整查詢結果的小數點位數

ROUND(REAL, n_digits)

假如只想要取小數點後三位

SELECT *,
      ROUND(Weight/((Height*0.01)*(Height*0.01)),3)AS BMI
 FROM data
 LIMIT 10;


可以看到最後指出來小數點後三位

COALESCE :將遺漏值轉換為指定常數

COALESCE(NULL, replacement)

舉例來說,在 Netflix 的 record 資料表中有許多director沒有被記錄到

再看一次這筆資料前十筆的紀錄
可以看到director的欄位內有很多空格

利用COALESCE來替換指定的常數

LENGTH :計算文字中有幾個字元,包含空格、標點符號

LENGTH(TEXT)

假設現在想知道每個title的文字長度

SELECT title,
       LENGTH(title) AS length_of_title
 FROM record
 LIMIT 10;

LOWER 與 UPPER : 調整英文的大小寫

LOWER(TEXT)
UPPER(TEXT)
SELECT title,
       UPPER(title) AS upper_title,
       LOWER(title) AS lower_title
 FROM record
 LIMIT 10;

聚合函數

常見的聚合函數

  • AVG(column_name):變數平均數
  • COUNT(column_name):計算變數的「非」遺漏值數
  • COUNT(*):計算觀測值數
  • MAX(column_name):最大值
  • MIN(column_name):最小值
  • SUM(column_name):計算變數的加總

計算平均身高和體重

SELECT AVG(Height) AS avg_height,
       AVG(Weight) AS avg_weight
 FROM data
 LIMIT 10;

找出身高最高和最矮的

SELECT MAX(Height) AS highest,
       MIN(Height) AS shortest
 FROM data
 LIMIT 10;


上一篇
{DAY8} SQLite基礎語法
下一篇
{DAY10} SQL查詢語法2
系列文
從資料庫到資料分析視覺化30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言